"""
Database session management following SOLID principles.
"""

import os
from typing import Generator, Optional
from contextlib import contextmanager
from sqlalchemy import create_engine, event
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.pool import StaticPool

from ..logging import get_logger


class DatabaseConfig:
    """Database configuration."""
    
    def __init__(
        self,
        database_url: Optional[str] = None,
        echo: bool = False,
        pool_size: int = 10,
        max_overflow: int = 20,
        pool_timeout: int = 30,
        pool_recycle: int = 3600
    ):
        self.database_url = database_url or os.getenv(
            "DATABASE_URL", 
            "sqlite:///./app.db"
        )
        self.echo = echo
        self.pool_size = pool_size
        self.max_overflow = max_overflow
        self.pool_timeout = pool_timeout
        self.pool_recycle = pool_recycle


class DatabaseSession:
    """Database session manager."""
    
    def __init__(self, config: Optional[DatabaseConfig] = None):
        self.config = config or DatabaseConfig()
        self.logger = get_logger("DatabaseSession")
        self._engine = None
        self._session_factory = None
        self._setup_database()
    
    def _setup_database(self):
        """Setup database engine and session factory."""
        try:
            # Create engine with appropriate configuration
            if self.config.database_url.startswith("sqlite"):
                self._engine = create_engine(
                    self.config.database_url,
                    echo=self.config.echo,
                    poolclass=StaticPool,
                    connect_args={"check_same_thread": False}
                )
            else:
                self._engine = create_engine(
                    self.config.database_url,
                    echo=self.config.echo,
                    pool_size=self.config.pool_size,
                    max_overflow=self.config.max_overflow,
                    pool_timeout=self.config.pool_timeout,
                    pool_recycle=self.config.pool_recycle
                )
            
            # Add event listeners
            self._add_event_listeners()
            
            # Create session factory
            self._session_factory = sessionmaker(
                bind=self._engine,
                autocommit=False,
                autoflush=False
            )
            
            self.logger.info(
                "Database connection established",
                database_url=self._mask_password(self.config.database_url)
            )
            
        except Exception as exc:
            self.logger.error(
                "Failed to setup database",
                error=str(exc),
                database_url=self._mask_password(self.config.database_url),
                exc_info=True
            )
            raise
    
    def _add_event_listeners(self):
        """Add database event listeners."""
        
        @event.listens_for(self._engine, "connect")
        def set_sqlite_pragma(dbapi_connection, connection_record):
            """Set SQLite pragmas for better performance."""
            if self.config.database_url.startswith("sqlite"):
                cursor = dbapi_connection.cursor()
                cursor.execute("PRAGMA foreign_keys=ON")
                cursor.execute("PRAGMA journal_mode=WAL")
                cursor.execute("PRAGMA synchronous=NORMAL")
                cursor.execute("PRAGMA cache_size=1000")
                cursor.execute("PRAGMA temp_store=MEMORY")
                cursor.close()
        
        @event.listens_for(self._engine, "before_cursor_execute")
        def log_sql(conn, cursor, statement, parameters, context, executemany):
            """Log SQL statements in debug mode."""
            if self.config.echo:
                self.logger.debug(
                    "Executing SQL",
                    statement=statement,
                    parameters=parameters
                )
    
    def _mask_password(self, url: str) -> str:
        """Mask password in database URL for logging."""
        if "://" in url and "@" in url:
            parts = url.split("://")
            if len(parts) == 2:
                scheme = parts[0]
                rest = parts[1]
                if "@" in rest:
                    auth_part, host_part = rest.split("@", 1)
                    if ":" in auth_part:
                        user, _ = auth_part.split(":", 1)
                        return f"{scheme}://{user}:***@{host_part}"
        return url
    
    @property
    def engine(self):
        """Get database engine."""
        return self._engine
    
    def get_session(self) -> Session:
        """Get database session."""
        if not self._session_factory:
            raise RuntimeError("Database not initialized")
        return self._session_factory()
    
    @contextmanager
    def session_scope(self):
        """Provide a transactional scope around a series of operations."""
        session = self.get_session()
        try:
            yield session
            session.commit()
        except Exception as exc:
            session.rollback()
            self.logger.error(
                "Database transaction failed",
                error=str(exc),
                exc_info=True
            )
            raise
        finally:
            session.close()
    
    def create_tables(self):
        """Create all tables."""
        try:
            from .base import Base
            Base.metadata.create_all(bind=self._engine)
            self.logger.info("Database tables created successfully")
        except Exception as exc:
            self.logger.error(
                "Failed to create tables",
                error=str(exc),
                exc_info=True
            )
            raise
    
    def drop_tables(self):
        """Drop all tables."""
        try:
            from .base import Base
            Base.metadata.drop_all(bind=self._engine)
            self.logger.info("Database tables dropped successfully")
        except Exception as exc:
            self.logger.error(
                "Failed to drop tables",
                error=str(exc),
                exc_info=True
            )
            raise
    
    def check_connection(self) -> bool:
        """Check database connection."""
        try:
            with self.session_scope() as session:
                session.execute("SELECT 1")
            return True
        except Exception as exc:
            self.logger.error(
                "Database connection check failed",
                error=str(exc)
            )
            return False
    
    def close(self):
        """Close database connections."""
        if self._engine:
            self._engine.dispose()
            self.logger.info("Database connections closed")


# Global database session instance
_db_session: Optional[DatabaseSession] = None


def init_database(config: Optional[DatabaseConfig] = None) -> DatabaseSession:
    """Initialize database session."""
    global _db_session
    _db_session = DatabaseSession(config)
    return _db_session


def get_database_session() -> DatabaseSession:
    """Get database session instance."""
    global _db_session
    if _db_session is None:
        _db_session = DatabaseSession()
    return _db_session


def get_db() -> Generator[Session, None, None]:
    """Dependency to get database session for FastAPI."""
    db_session = get_database_session()
    db = db_session.get_session()
    try:
        yield db
    finally:
        db.close()


def get_db_session() -> Session:
    """Get database session directly."""
    db_session = get_database_session()
    return db_session.get_session()


class DatabaseHealthCheck:
    """Database health check utility."""
    
    def __init__(self, db_session: DatabaseSession):
        self.db_session = db_session
        self.logger = get_logger("DatabaseHealthCheck")
    
    def check_health(self) -> dict:
        """Check database health."""
        try:
            # Test connection
            is_connected = self.db_session.check_connection()
            
            # Get connection pool info
            pool_info = self._get_pool_info()
            
            status = "healthy" if is_connected else "unhealthy"
            
            return {
                "status": status,
                "connected": is_connected,
                "pool_info": pool_info
            }
            
        except Exception as exc:
            self.logger.error(
                "Health check failed",
                error=str(exc),
                exc_info=True
            )
            return {
                "status": "unhealthy",
                "connected": False,
                "error": str(exc)
            }
    
    def _get_pool_info(self) -> dict:
        """Get connection pool information."""
        try:
            pool = self.db_session.engine.pool
            return {
                "size": pool.size(),
                "checked_in": pool.checkedin(),
                "checked_out": pool.checkedout(),
                "overflow": pool.overflow(),
                "invalid": pool.invalid()
            }
        except Exception:
            return {}